package com.zdkj.common.core.exceltools;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;

/**
 * @ProjectName: zdkj
 * @Package: com.zdkj.subway.st.util.exceltools
 * @ClassName: ExcelUtil
 * @Description:
 * @Author: kangjian
 * @CreateDate: 2020/1/12 9:21
 * @Version: 1.0
 */
@Slf4j
public class ExcelUtil {
    /**
     * 根据文件选择excel版本
     * @return
     * @throws Exception
     */
    public static Workbook chooseWorkbook(File file) throws Exception{
        Workbook workbook=null;
        if (!file.exists()) {
            return null;
        }
        try {
            if (file.getName().toLowerCase().endsWith(ExcelVersion.XLS.getFileSuffix())) {
                workbook= new HSSFWorkbook(new POIFSFileSystem(new FileInputStream(file)));
            } else if (file.getName().toLowerCase().endsWith(ExcelVersion.XLSX.getFileSuffix())) {
                workbook= new XSSFWorkbook(new FileInputStream(file));
            }
        }catch (Exception e)
        {
            log.error("",e);
        }
        return workbook;
    }


    /**
     * 根据选择生成Excel版本类型
     * @return
     * @throws Exception
     */
    public static Workbook createWorkbook(ExcelVersion excelVersion){
        Workbook workbook=null;
        try {
            if (excelVersion.equals(ExcelVersion.XLS)) {
                workbook= new HSSFWorkbook();
            } else if (excelVersion.equals(ExcelVersion.XLSX)) {
                workbook= new XSSFWorkbook();
            }
        }catch (Exception e)
        {
            log.error("",e);
        }
        return workbook;
    }

    /**
     * 缓存bean中，预定义Excel列头和bean中属性的关系，并进行排序
     * @param obj
     * @param <T>
     * @return
     */
    public static <T> List<ColInfo> getBeanColbyOder(T obj) throws IllegalAccessException {
        Class clazz = obj.getClass();
        Field[] fields = clazz.getDeclaredFields();
        List<ColInfo> list=new ArrayList<>();
        for (Field f : fields) {
            ColInfo colInfo=new ColInfo();
            f.setAccessible(true);
            //判断是否有FieldToDbColumn注解没有则舍弃
            HeaderToColumn anno = f.getAnnotation(HeaderToColumn.class);
            //存储bean中配置excel表头信息
            if(anno!=null&&anno.header()!=null)
            {
                colInfo.setColname(f.getName());
                colInfo.setDisplayname(anno.header().trim());
                colInfo.setIndex(anno.index());
                colInfo.setColhandlebysql(anno.colhandlebysql());
                list.add(colInfo);
            }
        }

        Collections.sort(list, new Comparator<ColInfo>() {
            @Override
            public int compare(ColInfo o1, ColInfo o2) {
                int result=1;
                if(o1.getIndex()>=o2.getIndex())
                {
                    result=0;
                }
                return result;
            }
        });

        return list;
    }

    /**
     * 缓存bean中，预定义Excel列头和bean中属性的关系
     * @param obj
     * @param <T>
     * @return
     */
    public static <T> Map<String, String> getBeanColumntoHeader(T obj) throws IllegalAccessException {
        Class clazz = obj.getClass();
        Field[] fields = clazz.getDeclaredFields();
        Map<String,String> beantocolumn=new HashMap<>();
        for (Field f : fields) {
            f.setAccessible(true);
            //判断是否有FieldToDbColumn注解没有则舍弃
            HeaderToColumn anno = f.getAnnotation(HeaderToColumn.class);
            //存储bean中配置excel表头信息
            if(anno!=null&&anno.header()!=null)
            {
                beantocolumn.put(f.getName(),anno.header().trim());
            }
        }
        return beantocolumn;
    }

    /**
     * 根据类的泛型和bean的属性类型，动态赋值
     * @param tmp
     * @param value
     * @param column
     * @param <T>
     * @throws NoSuchFieldException
     * @throws IllegalAccessException
     */
    public static  <T> void setFileValue(T tmp,String value,String column) throws NoSuchFieldException, IllegalAccessException {
        Field name = tmp.getClass().getDeclaredField(column);
        name.setAccessible(true);
        String type=name.getGenericType().toString();
        log.info("type={}",type);
        switch (type) {
            case "char":
            case "java.lang.Character":
            case "class java.lang.String":
                name.set(tmp, value);
                break;
            case "int":
            case "class java.lang.Integer":
                name.set(tmp, Integer.valueOf(value));
                break;
            case "float":
                name.set(tmp, Float.valueOf(value));
                break;
            case "double":
                name.set(tmp, Double.valueOf(value));
                break;
            case "class java.lang.Double":
                name.set(tmp, Double.valueOf(value));
                break;
            case "class java.lang.Float":
                name.set(tmp, Float.valueOf(value));
                break;
            case "class java.lang.Long":
                name.set(tmp, Long.valueOf(value));
                break;
            case "class java.lang.Short":
                name.set(tmp, Short.valueOf(value));
                break;
            case "class java.math.BigDecimal":
                if (value==""){
                    name.set(tmp, new BigDecimal("0.0"));
                    break;
                }else {
                    name.set(tmp, Double.valueOf(value));
                    break;}
            case "class java.time.LocalDateTime":
                if (value==""){
                    name.set(tmp, LocalDateTime.now());
                    break;
                }else {
                LocalDateTime stringToLocalDateTime =
                        LocalDateTime.parse(value, DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
                name.set(tmp, stringToLocalDateTime);
                break;}
            case "class java.time.LocalDate":
                LocalDate stringToLocalDate =
                        LocalDate.parse(value, DateTimeFormatter.ofPattern("yyyy-MM-dd"));
                name.set(tmp, stringToLocalDate);
                break;
            default:
                break;
        }
    }

    /**
     * 根据类的泛型和bean的属性类型，动态赋值
     * @param t
     * @param value
     * @param column
     * @param <T>
     * @throws NoSuchFieldException
     * @throws IllegalAccessException
     */
    public static  <T> void setCellValue(T t, Cell cell, Object value, String column) throws NoSuchFieldException, IllegalAccessException {

        Object obj=null;
        Field name = t.getClass().getDeclaredField(column);
        name.setAccessible(true);
        String type=name.getGenericType().toString();
        log.info("type={}",type);
        switch (type) {
            case "char":
            case "java.lang.Character":
            case "class java.lang.String":
                cell.setCellValue((String) value);
                break;
            case "int":
            case "class java.lang.Integer":
            case "class java.lang.Long":
                cell.setCellValue((Integer) value);
                break;
            case "float":
                cell.setCellValue((Double) value);
                break;
            case "double":
                cell.setCellValue((Double) value);
                break;
            case "class java.lang.Double":
                cell.setCellValue((Double) value);
                break;
            case "class java.lang.Float":
                cell.setCellValue((Double) value);
                break;
            case "class java.lang.Short":
                cell.setCellValue((Double) value);
                break;
            case "class java.math.BigDecimal":
                cell.setCellValue((Double) value);
                break;
            default:
                break;
        }
    }

    public static CellStyle getHeaderCellStyle(Workbook wb){
        CellStyle headerStyle = wb.createCellStyle();
  //      headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
  //      headerStyle.setFillPattern(IndexedColors.AUTOMATIC.index);
        headerStyle.setFillForegroundColor(IndexedColors.AQUA.index);
        headerStyle.setFillBackgroundColor(IndexedColors.GREEN.index);
        Font headerFont = wb.createFont();
        headerFont.setFontHeightInPoints((short) 14);
 //       headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        headerFont.setFontName("黑体");
        headerStyle.setFont(headerFont);
        return headerStyle;
    }

    public static CellStyle getCellStyle(Workbook wb){
        CellStyle headerStyle = wb.createCellStyle();
    //    headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 设置边框颜色
        headerStyle.setFillForegroundColor(IndexedColors.SKY_BLUE.index);
        headerStyle.setFillBackgroundColor(IndexedColors.SKY_BLUE.index);
        Font headerFont = wb.createFont();
        headerFont.setFontHeightInPoints((short) 14);
//        headerFont.setBoldweight(HSSFFont.COLOR_NORMAL);
        headerFont.setFontName("黑体");
        headerStyle.setFont(headerFont);
        return headerStyle;
    }

}
